using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace hxSec.StaticPage
{
    public class ChartData
    {
        public ChartData() { }

        /// <summary>
        /// 根据客户号获取饼图数据
        /// </summary>
        /// <param name="accountID"></param>
        /// <param name="labelID"></param>
        /// <returns></returns>
        public DataTable GetPieChartData(string accountID, string labelID)
        {
            string sqlstr = GetChartDataSql(labelID).Replace("@ACCOUNTCODE", accountID);
            DataTable dt = new DataTable();
            using (SqlDataReader rq = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringHx, CommandType.Text, sqlstr, null))
            {
                dt.Load(rq);
            }
            return dt;
        }

        /// <summary>
        /// 根据客户号获取折线图数据
        /// </summary>
        /// <param name="accountID"></param>
        /// <param name="labelID"></param>
        /// <returns></returns>
        public DataSet GetLineChartData(string accountID, string labelID)
        {
            string sqlstr = GetChartDataSql(labelID).Replace("@ACCOUNTCODE", accountID);
            //DataTable dt = new DataTable();
            //using (SqlDataReader rq = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringHx, CommandType.Text, sqlstr, null))
            //{
            //    dt.Load(rq);
            //}
            //return dt;
            return SqlHelper.GetDataSetBySQL(SqlHelper.ConnectionStringHx, sqlstr);
        }

        /// <summary>
        /// 技术折线图数据
        /// </summary>
        /// <param name="stockCode"></param>
        /// <param name="labelID"></param>
        /// <returns></returns>
        public DataSet GettTecLineChartData(string stockCode, string labelID)
        {
            string sqlstr = GetChartDataSql(labelID).Replace("@STOCKCODE", stockCode);
            return SqlHelper.GetDataSetBySQL(SqlHelper.ConnectionStringHx, sqlstr);
        }

        #region 根据labelID获取图形SQL
        /// <summary>
        /// 获取图表数据SQL
        /// </summary>
        /// <param name="labelID"></param>
        /// <returns></returns>
        private string GetChartDataSql(string labelID)
        {
            string strResult = string.Empty;
            string sqlstr = string.Format("select top 1 SQLSTR from TblbLabelCase where LABELID = '{0}'", labelID);
            using (SqlDataReader rq = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringHx, CommandType.Text, sqlstr, null))
            {
                while (rq.Read())
                {
                    strResult = rq["SQLSTR"] == DBNull.Value ? "" : rq["SQLSTR"].ToString();
                }
            }
            return strResult;
        }
        #endregion

        #region 获取所有计算标签
        /// <summary>
        /// 获取所有计算标签
        /// </summary>
        /// <returns></returns>
        public DataTable GetChartLabelData(int picType) 
        {
            DataTable dt = new DataTable();
            string sqlstr = string.Format("SP_CHARTLABEL_SELECT {0}", picType);
            using (SqlDataReader rq = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringHx, CommandType.Text, sqlstr, null))
            {
                dt.Load(rq);
            }
            return dt;
        }
        #endregion

        #region 判断客户的持仓情况
        public bool IsHoldByStockType(string type, string account) 
        {
            string sqlstr = string.Format("SP_LISTCHART_CCLB '{0}',{1}", type, account);
            using (SqlDataReader rq = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringHx, CommandType.Text, sqlstr, null))
            {
                if (rq.Read())
                    return true;
                else
                    return false;
            }
        }
        #endregion

        #region 待处理的客户信息和其状态的变化
        /// <summary>
        /// 获取待处理的客户信息
        /// </summary>
        /// <param name="iCount"></param>
        /// <returns></returns>
        public DataTable GetWaitTreatCustomerInfo(int pageIndex, int pageSize)
        {
            string sqlstr = string.Format("SP_WAITTREATCUSTOMER_SELECT {0}, {1}", pageIndex, pageSize);
            DataTable dt = new DataTable();
            using (SqlDataReader rq = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringHx, CommandType.Text, sqlstr, null))
            {
                dt.Load(rq);
            }
            return dt;
        }
        #endregion

        #region 将生成图插入到数据库
        public bool InsertChartToDB(GraphyDataInfo info, out string error)
        {
            error = string.Empty;
            try
            {
                SqlConnection sqlConn = new SqlConnection(SqlHelper.ConnectionStringHx);
                sqlConn.Open();
                SqlCommand insert = new SqlCommand("SP_GraphyData_Insert", sqlConn);
                insert.CommandType = CommandType.StoredProcedure;

                SqlParameter accountParameter = 
                        insert.Parameters.Add("@ACCOUNTCODE", SqlDbType.VarChar, 100);
                accountParameter.Value = info.ACCOUNTCODE.ToString();

                SqlParameter garphyParameter =
                        insert.Parameters.Add("@GRAPHYID", SqlDbType.Int, 4);
                garphyParameter.Value = Convert.ToInt32(info.GRAPHYID);

                SqlParameter labelParameter = 
                        insert.Parameters.Add("@LABELID", SqlDbType.Int, 4);
                labelParameter.Value = Convert.ToInt32(info.LABELID);

                SqlParameter fileParameter = 
                        insert.Parameters.Add("@FILENAME", SqlDbType.VarChar, 128);
                fileParameter.Value = info.FILENAME;

                SqlParameter imageParameter =
                        insert.Parameters.Add("@NR", SqlDbType.Binary);

                imageParameter.Value = info.NR;
                imageParameter.Size = info.NR.Length;

                SqlParameter dateParameter = 
                        insert.Parameters.Add("@OPERATIONDATE", SqlDbType.DateTime);
                dateParameter.Value = Convert.ToDateTime(info.OPERATIONDATE);

                int i = insert.ExecuteNonQuery();
                sqlConn.Close();
                return true;
            }
            catch (Exception exception)
            {
                error = exception.Message;
                return false;
            }
        }

        public bool InsertTecChartToDB(IndexesGraphyDataInfo info, out string error)
        {
            error = string.Empty;
            try
            {
                SqlConnection sqlConn = new SqlConnection(SqlHelper.ConnectionStringHx);
                sqlConn.Open();
                SqlCommand insert = new SqlCommand("SP_IndexesGraphyData_Insert", sqlConn);
                insert.CommandType = CommandType.StoredProcedure;

                SqlParameter accountParameter =
                        insert.Parameters.Add("@STOCKNAME", SqlDbType.VarChar, 100);
                accountParameter.Value = info.STOCKNAME.ToString();

                SqlParameter garphyParameter =
                        insert.Parameters.Add("@STOCKTYPE", SqlDbType.Int, 4);
                garphyParameter.Value = Convert.ToInt32(info.STOCKTYPE);

                SqlParameter labelParameter =
                        insert.Parameters.Add("@LABELID", SqlDbType.Int, 4);
                labelParameter.Value = Convert.ToInt32(info.LABELID);

                SqlParameter fileParameter =
                        insert.Parameters.Add("@FILENAME", SqlDbType.VarChar, 128);
                fileParameter.Value = info.FILENAME;

                SqlParameter imageParameter =
                        insert.Parameters.Add("@NR", SqlDbType.Binary);

                imageParameter.Value = info.NR;
                imageParameter.Size = info.NR.Length;

                SqlParameter dateParameter =
                        insert.Parameters.Add("@OPERATIONDATE", SqlDbType.DateTime);
                dateParameter.Value = Convert.ToDateTime(info.OPERATIONDATE);

                int i = insert.ExecuteNonQuery();
                sqlConn.Close();
                return true;
            }
            catch (Exception exception)
            {
                error = exception.Message;
                return false;
            }
        }
        #endregion

        #region 获取证券代码表
        public DataTable GetStockCodeList(int stocktype)
        {
            DataSet ds = new DataSet();
            switch (stocktype)
            {
                case 1:  // 股票
                    ds = SqlHelper.GetDataSetBySQL(SqlHelper.ConnectionStringHx, string.Format("SP_GETSTOCKCODE_SELECT {0}", stocktype));
                    break;
                case 2:　// 基金
                    ds = SqlHelper.GetDataSetBySQL(SqlHelper.ConnectionStringHx, string.Format("SP_GETSTOCKCODE_SELECT {0}", stocktype));
                    break;
                case 3: // 债券
                    ds = SqlHelper.GetDataSetBySQL(SqlHelper.ConnectionStringHx, string.Format("SP_GETSTOCKCODE_SELECT {0}", stocktype));
                    break;
                case 4: // 权证
                    ds = SqlHelper.GetDataSetBySQL(SqlHelper.ConnectionStringHx, string.Format("SP_GETSTOCKCODE_SELECT {0}", stocktype));
                    break;
                default:
                    break;
            }

            return ds.Tables[0];
        }

        #endregion

        #region 更新控制表
        /// <summary>
        /// 更新状态
        /// </summary>
        /// <param name="id"></param>
        /// <param name="labelid"></param>
        /// <returns></returns>
        public bool UpdateControlState(string id, string labelid)
        {
            try 
            {
                SqlConnection sqlConn = new SqlConnection(SqlHelper.ConnectionStringHx);
                sqlConn.Open();
                SqlCommand insert = new SqlCommand(string.Format("UPDATE TblbLabelProcessControl SET STATE = 1 WHERE LABELID = '" + labelid + "' AND GRAPHYID = '" + id + "'"), sqlConn);

                int i = insert.ExecuteNonQuery();
                sqlConn.Close();
                return true;
            }
            catch (Exception exception)
            {
                Console.Write(exception.Message);
                return false;
            }
        }

        /// <summary>
        /// 更新次数
        /// </summary>
        /// <param name="id"></param>
        /// <param name="labelid"></param>
        /// <returns></returns>
        public bool UpdateControlTimes(string id, string labelid)
        {
            try
            {
                SqlConnection sqlConn = new SqlConnection(SqlHelper.ConnectionStringHx);
                sqlConn.Open();
                SqlCommand insert = new SqlCommand(string.Format("UPDATE TblbLabelProcessControl SET TIMES = TIMES + 1 WHERE LABELID = '" + labelid + "' AND GRAPHYID = '" + id + "'"), sqlConn);

                int i = insert.ExecuteNonQuery();
                sqlConn.Close();
                return true;
            }
            catch (Exception exception)
            {
                Console.Write(exception.Message);
                return false;
            }
        }
        #endregion
    }
}
